﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;

namespace Follow.DataAccess
{
    public class RelatorioVisitasDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public RelatorioVisitasDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<RelatorioVisitas> findAll(string Mes, string Ano)
        {
            IList<RelatorioVisitas> relatorioVisitas = new List<RelatorioVisitas>();
            try
            {
                conn.Open();
                String sqlCommand = @"SELECT [funcionario_idfuncionario]
	                                  ,[funcionario].nome nomeFuncionario
	                                  ,month([datavisita]) mes
	                                  ,year([datavisita]) ano
	                                  ,count(*) qtde
                                  FROM [visita] join [funcionario] on [visita].funcionario_idfuncionario = [funcionario].idfuncionario
                                  where month([datavisita]) = " + Mes + @" and year([datavisita]) = " + Ano + @"
                                  group by  [funcionario].nome
                                      ,[funcionario_idfuncionario]
                                      ,month([datavisita]) 
	                                  ,year([datavisita])";


                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    RelatorioVisitas rv = new RelatorioVisitas();

                    rv.IDFuncionario = reader.GetInt32(0);
                    rv.NomeFuncionario = reader.GetString(1);
                    rv.Mes = reader.GetInt32(2);
                    rv.Ano = reader.GetInt32(3);
                    rv.QuantidadeVisita = reader.GetInt32(4);
                    relatorioVisitas.Add(rv);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return relatorioVisitas;

        }
    }
}